package com.geekstudy.week5.service;


import com.geekstudy.week5.tool.JdbcTool;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.Map;

@Service
public class Exercise10PrepareStatementService {

    public Map<String, Object> prepareStatement() throws SQLException {
        Map<String, Object> resultMap = new LinkedHashMap<String, Object>();

        Connection connection = new JdbcTool().getJdbcConnection();
        // 开启事务
        connection.setAutoCommit(false);

        Statement statement = connection.createStatement();
        boolean deleteResult = statement.execute("delete from geek_study where id in ('pre1001', 'pre1002', 'pre1003', 'pre1004', 'pre1005') ");
        resultMap.put("deleteResult", deleteResult);
        statement.close();

        PreparedStatement insertStatement = connection.prepareStatement("insert into geek_study (id, name, nick_name, address) values (?, ?, ?, ?)");



        for(int size = 1; size <=5; size++) {
            insertStatement.setString(1, "pre100" + size);
            insertStatement.setString(2, "name100" + size);
            insertStatement.setString(3, "nickName100" + size);
            insertStatement.setString(4, "address100" + size);
            insertStatement.addBatch();
        }
        int[] batchInsertResult = insertStatement.executeBatch();
        resultMap.put("batchInsertResult", batchInsertResult);
        insertStatement.close();

        PreparedStatement updateStatement = connection.prepareStatement("update geek_study set name = ? where id = ? ");
        boolean[] resultArray = new boolean[3];
        for(int size = 1; size <=3; size++) {
            updateStatement.setString(1, "UpdateName100" + size);
            updateStatement.setString(2, "pre100" + size);
            boolean updateResult = updateStatement.execute();
            resultArray[size - 1] = updateResult;
        }

        resultMap.put("batchUpdateResult", resultArray);
        updateStatement.close();

        connection.setAutoCommit(true);

        connection.close();


        return resultMap;

    }
}
